package org.damazio.flight.checklists.data;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class CheckListsSchema {

  private static final String DB_NAME = "checklists";
  private static final int DB_VERSION = 1;

  public static final String TYPE_COLUMN = "type";
  public static final String ITEM_TYPE = "item";
  public static final String LIST_TYPE = "list";

  public static final String ID_COLUMN = "_id";
  public static final String PARENT_ID_COLUMN = "parent_id";
  public static final String NAME_COLUMN = "name";
  public static final String ORDERED_COLUMN = "ordered";

  private static final String COMBINED_TABLE_NAME = "checklists_and_items";
  private static final String CREATE_COMBINED_VIEW_SQL =
      "CREATE VIEW IF NOT EXISTS " + COMBINED_TABLE_NAME + " AS " +
          CheckListsTable.QUERY_SQL +
          " UNION " +
          CheckListItemsTable.QUERY_SQL;

  private final Context context;
  private SQLiteDatabase db;
  private CheckListsTable checkListsTable;
  private CheckListItemsTable checkListItemsTable;

  public CheckListsSchema(Context context) {
    this.context = context;
  }

  public void openOrCreate() {
    db = context.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE, null);

    int version = db.getVersion();
    if (version < DB_VERSION) {
      // Upgrade the schema here, when needed
      createDatabase();

      if (version == 0) {
        // First startup, create sample data
        SampleData.insertSampleDataInto(this);
      }

      db.setVersion(DB_VERSION);
    } else if (version > DB_VERSION) {
      // Should never ever happen - binary downgraded?
      // TODO: Crash more nicely
      throw new IllegalStateException("Unknown database version detected: " + version);
    }

    db.setLockingEnabled(false);
  }

  public void close() {
    db.close();
  }

  public CheckListsTable getCheckListsTable() {
    if (checkListsTable == null) {
      checkListsTable = new CheckListsTable(db);
    }
    return checkListsTable;
  }

  public CheckListItemsTable getCheckListItemsTable() {
    if (checkListItemsTable == null) {
      checkListItemsTable = new CheckListItemsTable(db);
    }
    return checkListItemsTable;
  }
  
  private void createDatabase() {
    db.beginTransaction();
    try {
      getCheckListsTable().create();
      getCheckListItemsTable().create();
      db.execSQL(CREATE_COMBINED_VIEW_SQL);
      db.setTransactionSuccessful();
    } finally {
      db.endTransaction();
    }
  }

  public Cursor findByParent(long parentId) {
    String parentIdStr = Long.toString(parentId);
    return db.query(COMBINED_TABLE_NAME,
        new String [] { ID_COLUMN, TYPE_COLUMN, NAME_COLUMN },
        PARENT_ID_COLUMN + "=?", new String[] { parentIdStr },
        null, null, ORDERED_COLUMN);
  }

  public void beginTransaction() {
    db.beginTransaction();
  }

  public void endTransaction() {
    db.endTransaction();
  }

  public boolean inTransaction() {
    return db.inTransaction();
  }

  public void setTransactionSuccessful() {
    db.setTransactionSuccessful();
  }
}
